package rest;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Random;

import javax.servlet.ServletException;

public class DBOperations {

	private static Connection connection;
	private static final String DB_Name = "restservicesdb";	
	private static final String DB_Driver = "com.mysql.jdbc.Driver";
	private static final String DB_CONNECTION_STRING = "jdbc:mysql://localhost/"+DB_Name;
	private static final String DB_USER = "root";
	private static final String DB_PASSWORD = "root";
	
	private static final String Mode_Of_Booking = "App";
	private static final String HowDoKnowUS = "google-adds";
	/*
	 * USER-REGISTRATION 
	 */
	protected boolean doUserRegistration_NPE(String name ,String phone ,String email, String password){
		
		/* TABLE- 'user_registration' || Column names=> 'user_id','username','email_address','phone_no','password','creationTime' */
		boolean status = false;
		System.out.println("inside doUserRegistration_NPE >>>");
		java.sql.PreparedStatement preparedStatement = null;
		String userId =  String.valueOf(new Random().nextInt(10000));
		String creationTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(Calendar.getInstance().getTime());
		
		String insertQuery = "insert into user_registration values(?,?,?,?,?,?)";
		try{
			//Get Connection & Statement
			connection =  getDBConnection();
			preparedStatement = connection.prepareStatement(insertQuery);
			preparedStatement.setString(1, userId);
			preparedStatement.setString(2,name);
			preparedStatement.setString(3,email);
			preparedStatement.setString(4,phone);
			preparedStatement.setString(5,password);
			preparedStatement.setString(6, creationTime);
				
			// execute insert SQL statement
			int result = preparedStatement.executeUpdate();
		//	System.out.println(" preparedStatement result >>> "+result);
		
			status = true;   // status = true in case DB OPeration Successful
			
		}catch(SQLException e){
			System.out.println(" <<< SQLException Caught >>> ");
			e.printStackTrace();
		}finally{

			try{
				if(preparedStatement!=null) preparedStatement.close();} catch(SQLException e){
				e.printStackTrace(); }
			try{
				if(null!=connection) connection.close(); } catch(SQLException e){
					e.printStackTrace(); }
			}
		return status;      // return success/ failure status...
		}
		

	protected boolean doProcessBooking(String name, String phone, String email,
			String pickUp, String destination, String pickUpDate,
			String pickUpTime, String planType) {
		
		/* TABLE- 'user_registration' || Column names=> 'CName','MobileNo','PickupA', DropA EmailAdd No_of_passengers  BookingTime PickupTime Remark Mode_Of_Booking BookingType CarType ReturnDate */
		boolean status = false;
		java.sql.PreparedStatement preparedStatement = null;
		String userId =  String.valueOf(new Random().nextInt(10000));
		String creationTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(Calendar.getInstance().getTime());
		
		/*insert into bookinginfo(CName,MobileNo,Mode_Of_Booking,HowDoKnowUS)values('prabhakar',787875424,'call','email');*/
		String insertQuery = "insert into bookinginfo(CName,MobileNo,Mode_Of_Booking,HowDoKnowUS)values(?,?,?,?)";
		try{
			//Get Connection & Statement
			connection =  getDBConnection();
			preparedStatement = connection.prepareStatement(insertQuery);
			preparedStatement.setString(1, name);
			preparedStatement.setString(2,phone);
			preparedStatement.setString(3,Mode_Of_Booking);
			preparedStatement.setString(4,HowDoKnowUS);
			preparedStatement.setString(5,email);
		//	preparedStatement.setString(6, );
			
			// execute insert SQL statement
			int result = preparedStatement.executeUpdate();
		//	System.out.println(" preparedStatement result >>> "+result);
		
			status = true;   // STATUS = TRUE in case DB OPeration Successful
			
		}catch(SQLException e){
			System.out.println(" <<< SQLException Caught >>> ");
			e.printStackTrace();
		}finally{

			try{
				if(preparedStatement!=null) preparedStatement.close();} catch(SQLException e){
				e.printStackTrace(); }
			try{
				if(null!=connection) connection.close(); } catch(SQLException e){
					e.printStackTrace(); }
			}
		
		return status;      // return success/ failure status...
	
	}	
	
	protected static void addDetails_NP(String name ,long phone)
			throws ServletException, IOException {

		System.out.println("inside addDetails_NP >>>");
		java.sql.PreparedStatement preparedStatement = null;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String currentDate = sdf.format(new Date());
		String insertQuery = "insert into demotable values (?,?,?)" ;
		
		String phoneNumber =  String.valueOf(phone);
		
		try{
			//Get Connection & Statement 
			connection = getDBConnection();
			
			 preparedStatement = connection.prepareStatement(insertQuery);
			 preparedStatement.setString(1,name);
			 preparedStatement.setString(2,phoneNumber);
			 preparedStatement.setString(3,currentDate);
			
			// execute insert SQL stetement
			preparedStatement.executeUpdate();
			
			System.out.println("insertQuery >>>"+insertQuery);
			System.out.println("after executing preparedStatement >>> ");
			
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			
			try{
				if(preparedStatement!=null) preparedStatement.close();} catch(SQLException e){
				e.printStackTrace(); }
			try{
				if(null!=connection) connection.close(); } catch(SQLException e){
					e.printStackTrace(); }
			}
		
	}


	private static Connection getDBConnection() {
		
		 try {
				Class.forName(DB_Driver).newInstance();
			} catch (InstantiationException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}
		 
			try{ 
			 connection = DriverManager.getConnection(DB_CONNECTION_STRING,DB_USER,DB_PASSWORD);
			}catch(SQLException e){
				e.printStackTrace();
			}
		return connection;
	}

	
}
